<%@ Page Language="C#" %>
<%@ OutputCache Duration="3600" VaryByParam="none" %>
<%@ Import Namespace="Teradata.Client.Provider" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected String CurrProdLine = String.Empty, CurrProd = String.Empty;
    protected Int32 SUB_MTD_CNT=0, SUB_PREV_MTD_CNT=0, SUB_PREV_MON=0, SUB_PACING_CNT=0;
    protected Int32 TOT_MTD_CNT=0, TOT_PREV_MTD_CNT=0, TOT_PREV_MON=0, TOT_PACING_CNT=0;
    
    protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            
            if (CurrProdLine != e.Row.Cells[0].Text)
            {
                Table tblTemp = (Table)this.DisplayGrid.Controls[0];
                int intIndex = tblTemp.Rows.GetRowIndex(e.Row);
                CurrProdLine = e.Row.Cells[0].Text;
                if (intIndex>1)
                {
                    GridViewRow gvr = new GridViewRow(intIndex, intIndex, DataControlRowType.Separator, DataControlRowState.Normal);
                    for(Int32 ctr=0; ctr<9; ctr++)
                    {
                        TableCell cellTemp = new TableCell();
                        cellTemp.Font.Bold = true;
                        cellTemp.HorizontalAlign = HorizontalAlign.Right;
                        cellTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#9BD0FF");
                        gvr.Cells.Add(cellTemp);
                        switch(ctr)
                        {
                            case 0:
                                break;
                            case 1:
                                cellTemp.Text = "SUBTOTAL";
                                cellTemp.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 2:
                                cellTemp.Text = SUB_MTD_CNT.ToString("n0");
                                break;
                            case 3:
                                cellTemp.Text = SUB_PREV_MTD_CNT.ToString("n0");
                                break;
                            case 4:
                                cellTemp.Text = SUB_PREV_MON.ToString("n0");
                                break;
                            case 5:
                                cellTemp.Text = SUB_PACING_CNT.ToString("n0");
                                break;
                            case 6:
                                if (SUB_PREV_MON != 0 )
                                    cellTemp.Text = Convert.ToDecimal(Convert.ToDecimal(SUB_PACING_CNT)/Convert.ToDecimal(SUB_PREV_MON)).ToString("p1");
                                else
                                    cellTemp.Text = "0.0%";
                                break;
                        }
                    }
                    
                    tblTemp.Controls.AddAt(intIndex, gvr);
                }
                SUB_MTD_CNT = 0;
                SUB_PREV_MTD_CNT = 0;
                SUB_PREV_MON = 0;
                SUB_PACING_CNT = 0;
            }
            else
            {
                e.Row.Cells[0].Text = "";
            }
            
            SUB_MTD_CNT += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"MTD_CNT"));
            SUB_PREV_MTD_CNT += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"PREV_MTD_CNT"));
            SUB_PREV_MON += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"PREV_MON_CNT"));
            SUB_PACING_CNT += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"PACING"));
           
            if (CurrProd != e.Row.Cells[1].Text)
            {
                CurrProd = e.Row.Cells[1].Text;
            }
            else
            {
                e.Row.Cells[1].Text = "";
            }
            
            TOT_MTD_CNT += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"MTD_CNT"));
            TOT_PREV_MTD_CNT += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"PREV_MTD_CNT"));
            TOT_PREV_MON += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"PREV_MON_CNT"));
            TOT_PACING_CNT += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem,"PACING"));
        }

        if (e.Row.RowType == DataControlRowType.Footer)
        {
            {
                Table tblTemp = (Table)this.DisplayGrid.Controls[0];
                int intIndex = tblTemp.Rows.GetRowIndex(e.Row);
                GridViewRow gvr = new GridViewRow(intIndex, intIndex, DataControlRowType.Separator, DataControlRowState.Normal);
                for (Int32 ctr = 0; ctr < 9; ctr++)
                {
                    TableCell cellTemp = new TableCell();
                    cellTemp.Font.Bold = true;
                    cellTemp.HorizontalAlign = HorizontalAlign.Right;
                    cellTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#9BD0FF");
                    gvr.Cells.Add(cellTemp);
                    switch (ctr)
                    {
                        case 0:
                            break;
                        case 1:
                            cellTemp.Text = "SUBTOTAL";
                            cellTemp.HorizontalAlign = HorizontalAlign.Left;
                            break;
                        case 2:
                            cellTemp.Text = SUB_MTD_CNT.ToString("n0");
                            break;
                        case 3:
                            cellTemp.Text = SUB_PREV_MTD_CNT.ToString("n0");
                            break;
                        case 4:
                            cellTemp.Text = SUB_PREV_MON.ToString("n0");
                            break;
                        case 5:
                            cellTemp.Text = SUB_PACING_CNT.ToString("n0");
                            break;
                        case 6:
                            if (SUB_PREV_MON != 0)
                                cellTemp.Text = Convert.ToDecimal(Convert.ToDecimal(SUB_PACING_CNT) / Convert.ToDecimal(SUB_PREV_MON)).ToString("p1");
                            else
                                cellTemp.Text = "0.0%";
                            break;
                    }
                }

                tblTemp.Controls.AddAt(intIndex, gvr);
            }
            e.Row.Cells[2].Text = TOT_MTD_CNT.ToString("n0");
            e.Row.Cells[3].Text = TOT_PREV_MTD_CNT.ToString("n0");
            e.Row.Cells[4].Text = TOT_PREV_MON.ToString("n0");
            e.Row.Cells[5].Text = TOT_PACING_CNT.ToString("n0");
            if (TOT_PREV_MON != 0)
                e.Row.Cells[6].Text = Convert.ToDecimal(Convert.ToDecimal(TOT_PACING_CNT) / Convert.ToDecimal(TOT_PREV_MON)).ToString("p1");
            else
                e.Row.Cells[6].Text = "0.0%";
        } 
    
    }

    protected void SalesData_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.CommandTimeout = 0;
    }

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Ranking Report</title>
</head>
<body style="font: normal 8pt Calabri, Arial, San-Serif;">
<form id="form1" runat="server">
   <p><h3>Ranking Report</h3><br /><%=DateTime.Today.ToString("MMMM yyyy") %> (<%=DateTime.Today.Day%> day of month)</p>
   
   <center>
   <asp:gridview runat="server" DataSourceID="SalesData" EnableViewState="False" onrowdatabound="GridView_RowDataBound" ShowFooter="True"
     EnableModelValidation="True" AutoGenerateColumns="False"  Font-Size="10pt" ID="DisplayGrid">
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <Columns>
         <asp:BoundField DataField="AREA_MGR" HeaderText="AREA_MGR"  FooterText="TOTAL">
             <ItemStyle HorizontalAlign="Left" Font-Bold="True" />
             <FooterStyle HorizontalAlign="Left" />
         </asp:BoundField>
         <asp:BoundField DataField="ACCT_MGR" HeaderText="ACCT_MGR"  FooterText="TOTAL">
             <ItemStyle HorizontalAlign="Left" Font-Bold="True" />
             <FooterStyle HorizontalAlign="Left" />
        </asp:BoundField>
         <asp:BoundField DataField="MTD_CNT" HeaderText="MTD COUNT">
          <HeaderStyle HorizontalAlign="Right" />
          <FooterStyle HorizontalAlign="Right" />
          <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
         <asp:BoundField DataField="PREV_MTD_CNT" HeaderText="PREV MTD CNT">
          <HeaderStyle HorizontalAlign="Right" />
          <FooterStyle HorizontalAlign="Right" />
          <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
         <asp:BoundField DataField="PREV_MON_CNT" HeaderText="PREV MONTH">
          <HeaderStyle HorizontalAlign="Right" />
          <FooterStyle HorizontalAlign="Right" />
          <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
         <asp:BoundField DataField="PACING" HeaderText="PACING">
          <HeaderStyle HorizontalAlign="Right" />
          <FooterStyle HorizontalAlign="Right" />
          <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
         <asp:BoundField DataField="PACE_RATE" HeaderText="PACE RATE" DataFormatString="{0:p1}" HtmlEncode="False">
          <HeaderStyle HorizontalAlign="Right" />
          <FooterStyle HorizontalAlign="Right" />
          <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
         <asp:BoundField DataField="ACCT_MGR_RANK" HeaderText="RANKING">
          <HeaderStyle HorizontalAlign="Center" />
          <FooterStyle HorizontalAlign="Center" />
          <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>
         <asp:BoundField DataField="AREA_MGR_RANK" HeaderText="GROUP RANK">
          <HeaderStyle HorizontalAlign="Center" />
          <FooterStyle HorizontalAlign="Center" />
          <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>

    </Columns>
    <FooterStyle Font-Bold="True" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#0A94D6" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="#dddddd" ForeColor="#284775" />
</asp:gridview>
</center>
 <asp:sqldatasource runat="server" ID="SalesData"
     ConnectionString="<%$ ConnectionStrings:EDW_GROUP_ID %>"
     ProviderName="Teradata.Client.Provider" OnSelecting="SalesData_Selecting"
     SelectCommand="SELECT D.AREA_MGR, D.ACCT_MGR, COALESCE(E.CNT, 0) MTD_CNT, COALESCE(J.CNT, 0) PREV_MTD_CNT,
 COALESCE(H.CNT, 0) PREV_MON_CNT, MTD_CNT* (G.LAST_DAY/F.DOM) PACING,
  CASE WHEN PREV_MON_CNT=0 THEN 0 ELSE CAST(PACING AS DECIMAL(20,4))/PREV_MON_CNT END PACE_RATE,
   RANK(MTD_CNT) ACCT_MGR_RANK, K.AREA_MGR_RANK FROM (SELECT AREA_MGR, ACCT_MGR FROM MIS.ACC_BASE_DATA WHERE ACCT_MGR GT '' 
GROUP BY 1,2) D
  INNER JOIN  (
   SELECT AREA_MGR, CNT, RANK(CNT) AREA_MGR_RANK FROM (
   SELECT B.AREA_MGR, COUNT(*) CNT
   FROM MIS.ACC_RAW A
   INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
   (SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE) C
   WHERE A.START_DT BETWEEN C.BOM AND C.EOM
   GROUP BY 1) Y) K ON D.AREA_MGR=K.AREA_MGR
  LEFT OUTER JOIN (
   SELECT B.AREA_MGR, B.ACCT_MGR, COUNT(*) CNT
   FROM MIS.ACC_RAW A
    INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
   (SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE) C
   WHERE A.START_DT BETWEEN C.BOM AND C.EOM
   GROUP BY 1,2) E ON D.AREA_MGR=E.AREA_MGR AND D.ACCT_MGR=E.ACCT_MGR
  LEFT OUTER JOIN (
   SELECT B.AREA_MGR, B.ACCT_MGR, COUNT(*) CNT
   FROM MIS.ACC_RAW A
    INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
   (SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)) C
   WHERE A.START_DT BETWEEN C.BOM AND C.EOM
   GROUP BY 1,2) J ON D.AREA_MGR=J.AREA_MGR AND D.ACCT_MGR=J.ACCT_MGR
  LEFT OUTER JOIN (
   SELECT B.AREA_MGR, B.ACCT_MGR, COUNT(*) CNT
   FROM MIS.ACC_RAW A
    INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
   (SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE
CALENDAR_DATE=CURRENT_DATE) C
   WHERE A.START_DT BETWEEN C.BOM AND C.EOM
   GROUP BY 1,2) H ON D.AREA_MGR=H.AREA_MGR AND D.ACCT_MGR=H.ACCT_MGR,
    (SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE
CALENDAR_DATE=CURRENT_DATE) F,
  (SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR WHERE MONTH_OF_CALENDAR=(
   SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE
CALENDAR_DATE=CURRENT_DATE)) G
ORDER BY 9,8;" ></asp:sqldatasource>
</form>
</body>
</html>
